package com.deng.etl.excel.impl;

import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelImport;
import com.deng.etl.excel.IExcelRule;
import com.deng.util.DateTools;

public class GongZuoJiXiaoGongZuoLiangImpl extends SQLHelper implements IExcelRule{

	public void importData(Workbook wb, String type_name,String id) {
		try{			
				Connection conn = getConnect();
				
				Sheet sh=wb.getSheet(0);
				int rownum = sh.getRows();
				
//				String stat_time1 = DateTools.dtsJustDate(DateTools.stdJustDate_xiegang(sh.getCell(0,2).getContents()));
//				String delSQL = "delete t_org_organization_item_count_month" +
//						"	where stat_time ='"+stat_time1+"'";
//				updateData(conn, delSQL);//删除stat_Time当天
				
				conn.setAutoCommit(false);
				
				String insertSQL = "insert into t_org_organization_item_count_month (create_time,sjly," +
						"stat_time,fee,org_item_count," +
						"org_fee_type_id,org_fee_type_name," +
						"org_item_id,org_item_name," +
						"org_operate_doctor_id,org_operate_doctor_name," +
						"org_operate_organization_id,org_operate_organization_name," +
						"org_order_doctor_id,org_order_doctor_name," +
						"org_order_organization_id,org_order_organization_name," +
						"org_work_group_id,org_work_group_name) " +
						"	values('"+DateTools.dtsWithTime(new Date())+"',0," +
								"?,?,?,?,?,?,?,?," +
								"?,?,?,?,?,?,?,?,?)";
				PreparedStatement pst =conn.prepareStatement(insertSQL);
				for(int i=2;i<rownum;i++){
					System.out.println("行： "+i);
					Cell c1 = sh.getCell(0,i);
					if(c1 == null){
						continue;
					}
					if(c1.getContents() == null || "".equals(c1.getContents())){
						continue;
					}
					Cell c2 = sh.getCell(1,i);
					Cell c3 = sh.getCell(2,i);
					Cell c4 = sh.getCell(3,i);
					Cell c5 = sh.getCell(4,i);
					Cell c6 = sh.getCell(5,i);
					Cell c7 = sh.getCell(6,i);
					Cell c8 = sh.getCell(7,i);
					Cell c9 = sh.getCell(8,i);
					Cell c10 = sh.getCell(9,i);
					Cell c11 = sh.getCell(10,i);
					Cell c12 = sh.getCell(11,i);
					Cell c13 = sh.getCell(12,i);
					Cell c14 = sh.getCell(13,i);
					Cell c15 = sh.getCell(14,i);
					Cell c16 = sh.getCell(15,i);
					Cell c17 = sh.getCell(16,i);
					
					System.out.println(c1.getContents());
					String stat_time_str = c1.getContents().trim()+"-"+c2.getContents().trim()+"-1";
					java.sql.Date stat_time = new java.sql.Date(DateTools.stdJustDate(stat_time_str).getTime());
					String org_item_id = c3.getContents();
					String org_item_name = c4.getContents();
					BigDecimal org_item_count= new BigDecimal(0);
					try{
						org_item_count = new BigDecimal(c5.getContents().trim());
					}catch(Exception ex){
						ex.printStackTrace();
					}
					String org_fee_type_id = c6.getContents();
					String org_fee_type_name = c7.getContents();
					String org_operate_doctor_id = c8.getContents();
					String org_operate_doctor_name = c9.getContents();
					String org_operate_organization_id = c10.getContents();
					String org_operate_organization_name = c11.getContents();
					String org_order_doctor_id = c12.getContents();
					String org_order_doctor_name = c13.getContents();
					String org_order_organization_id = c14.getContents();
					String org_order_organization_name = c15.getContents();
					String org_work_group_id = c16.getContents();
					String org_work_group_name = c17.getContents();
					BigDecimal fee = new BigDecimal(0);
					
					pst.setDate(1, stat_time);
					pst.setBigDecimal(2, fee);
					pst.setBigDecimal(3, org_item_count);
					pst.setString(4, org_fee_type_id);
					pst.setString(5, org_fee_type_name);
					pst.setString(6, org_item_id);
					pst.setString(7, org_item_name);
					pst.setString(8, org_operate_doctor_id);
					pst.setString(9, org_operate_doctor_name);
					pst.setString(10, org_operate_organization_id);
					pst.setString(11, org_operate_organization_name);
					pst.setString(12, org_order_doctor_id);
					pst.setString(13, org_order_doctor_name);
					pst.setString(14, org_order_organization_id);
					pst.setString(15, org_order_organization_name);
					pst.setString(16, org_work_group_id);
					pst.setString(17, org_work_group_name);
					pst.addBatch();
				}
				pst.executeBatch();
				conn.commit();
				pst.close();
				freeConnection(conn);

		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	
	

	
	
	

}
